package cn.swust.service.serviceimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.swust.model.InformationBean;
import cn.swust.service.InformationService;
import cn.swust.util.DBUtil;
import cn.swust.util.Pager;

public class InformationServiceImpl implements InformationService {

	public int addInfor(InformationBean infor) {
		int ret = 0;
		try{
		String insertSql = "insert into information(info_type,info_text,info_time,info_author,infor_title)" +
		" values (" + infor.getType() + ",'" + infor.getText() + "','" + infor.getTime() + "','" + infor.getAuthor()
		+ "','" + infor.getTitle() + "')";
		Connection conn = DBUtil.getConnection();
		Statement state = conn.createStatement();
		state.execute(insertSql, Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = state.getGeneratedKeys();
		while(rs.next())
			ret = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return ret;
	}

	public int delInforById(int id) {
		String delSql = "delete from information where info_id = " + id;
		return DBUtil.updateSql(delSql);
	}

	public int delInforByType(int type) {
		String delSql = "delete from information where info_type = " + type;
		return DBUtil.updateSql(delSql);
	}

	public int modInfor(int id, InformationBean infor) {
		String modSql = "update information set info_type = " + infor.getType() + ",info_text = '" +
		infor.getText() + "',info_time = '" + infor.getTime() + "',info_author = '" + infor.getAuthor() +
		"',infor_title = '" + infor.getTitle() + "' where info_id = " + id;
		return DBUtil.updateSql(modSql);
	}

	public InformationBean queryById(int id) {
		String querySql = "select * from information where info_id = " + id;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		InformationBean infor = new InformationBean();
		try {
			if (rs.next()) {
				infor.setId(rs.getInt("info_id"));
				infor.setType(rs.getInt("info_type"));
				infor.setText(rs.getString("info_text"));
				infor.setTime(rs.getString("info_time"));
				infor.setAuthor(rs.getString("info_author"));
				infor.setTitle(rs.getString("infor_title"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return infor;
	}

	public ArrayList<InformationBean> queryByType(Pager pager,int type) {
		String querySql = "select * from information where info_type = " + type +
		" limit " + (pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		if(type == -1)
			querySql = "select * from information limit " +
		(pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<InformationBean> inforList = new ArrayList<InformationBean>();
		try {
			while (rs.next()) {
				InformationBean infor = new InformationBean();
				infor.setId(rs.getInt("info_id"));
				infor.setType(rs.getInt("info_type"));
				infor.setText(rs.getString("info_text"));
				infor.setTime(rs.getString("info_time"));
				infor.setAuthor(rs.getString("info_author"));
				infor.setTitle(rs.getString("infor_title"));
				inforList.add(infor);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return inforList;
	}

	public int getCount(int type) {
		String insertSql = "select count(*) from information where info_type = " + type;
		if(type == -1)
			insertSql = "select count(*) from information";
		ResultSet rs = DBUtil.querySql(insertSql, DBUtil.creatStatement(DBUtil.getConnection()));
		int ret = 0;
		try {
			if (rs.next()) {
				ret = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	public ArrayList<InformationBean> queryCountByType(int count,int type) {
		String querySql = "select * from information where info_type = " + type +
		" order by info_time desc limit " + count;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<InformationBean> inforList = new ArrayList<InformationBean>();
		try {
			while (rs.next()) {
				InformationBean infor = new InformationBean();
				infor.setId(rs.getInt("info_id"));
				infor.setType(rs.getInt("info_type"));
				infor.setText(rs.getString("info_text"));
				infor.setTime(rs.getString("info_time"));
				infor.setAuthor(rs.getString("info_author"));
				infor.setTitle(rs.getString("infor_title"));
				inforList.add(infor);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return inforList;
	}

	public InformationBean queryByTitle(String title) {
		String querySql = "select * from information where infor_title = '" + title + "'";
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		InformationBean infor = new InformationBean();
		try {
			if (rs.next()) {
				infor.setId(rs.getInt("info_id"));
				infor.setType(rs.getInt("info_type"));
				infor.setText(rs.getString("info_text"));
				infor.setTime(rs.getString("info_time"));
				infor.setAuthor(rs.getString("info_author"));
				infor.setTitle(rs.getString("infor_title"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return infor;
	}

}
